--------------------------------------------------------------------------------
-- @name: sql and dependent objects
-- @author: dion cho
-- @note: test case for relating SQL and dependent objects
--------------------------------------------------------------------------------

@capture_on
set timing off
set echo on
set pages 10000

drop table t1 purge;
drop table t2 purge;

create table t1(c1 int, c2 int);
create index t1_n1 on t1(c1);

create table t2(c1 int, c2 int);

insert into t1
select level, level from dual
connect by level <= 1000;

insert into t2
select level, level from dual
connect by level <= 1000;

create or replace view v1
as
select
  t1.c1, t2.c2
from
  t1, t2
where
  t1.c1 = t2.c1
;


@gather t1
@gather t2

select * 
from t1, t2
where t1.c1 = t2.c1
      and t1.c1 between 1 and 10
;

select * 
from t1, t2
where t1.c1 = t2.c1
      and t1.c2 = 1
;

select * 
from v1
where c1 = 1
;

-- finding cursors dependent on the table t1
select from_address, from_hash
from v$object_dependency
where to_name = 'T1'
      and to_owner = 'UKJA';
      

select s.sql_text, s.sql_id, s.child_number
from v$sql s, v$object_dependency d
where
      d.from_address = s.address
      and d.from_hash = s.hash_value
      and d.to_name = 'T1'
      and d.to_owner = 'UKJA';
          

-- execute above 3 query many times
exec dbms_workload_repository.create_snapshot;

select
  p.plan_table_output
from
  v$sql s,
  v$object_dependency d,
  table(dbms_xplan.display_cursor(s.sql_id, s.child_number, 'basic')) p
where
  d.from_address = s.address
  and d.from_hash = s.hash_value
  and d.to_name = 'T1'
  and d.to_owner = 'UKJA';



select
  p.plan_table_output
from
  v$sql s,
  v$object_dependency d,
  table(dbms_xplan.display_awr(s.sql_id, null, null,'basic')) p
where
  d.from_address = s.address
  and d.from_hash = s.hash_value
  and d.to_name = 'T1'
  and d.to_owner = 'UKJA';




select * 
from v1
where c1 = 1
;

col to_owner format a10
col to_name format a30
select
  s.sql_id,
  d.to_owner,
  d.to_name,
  d.to_type
from 
  v$object_dependency d,
  v$sql s
where
  d.from_address = s.address
  and d.from_hash = s.hash_value
  and (s.address, s.hash_value) in (
      select prev_sql_addr, prev_hash_value
      from v$session 
      where sid = userenv('sid'))
;

set timing on

@capture_off

